﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;

using Model;

namespace DAL
{
    public class DimensionDAL
    {
        /// <summary>
        /// 获得维度分类列表
        /// </summary>
        public static DataSet GetDimensionClass()
        {
            string strSql = @"select * from dbo.tb_Dimension_Class";
            return DbHelperSQL.Query(strSql.ToString());
        }
       

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public static DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"select 
                            dimension_id,
                            dimension_name,
                            dimension_description,
                            dimension_class_nm,
                            dimension_time_min,
                            (select count(*)  from dbo.tb_Question where question_dimensionid = dimension_id and question_status = 1) as question_num
                           ");
            if (strWhere.Trim() == "")
            {
                strSql.Append(@"FROM dbo.tb_Dimension,dbo.tb_Dimension_Class 
                                where dbo.tb_Dimension.dimension_class_id = dbo.tb_Dimension_Class.dimension_class_id");
            }
            else
            {
                strSql.Append(@" FROM dbo.tb_Dimension,dbo.tb_Dimension_Class  where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());

        }


        /// <summary>
        /// 获得数据列表
        /// </summary>
        public static DataSet GetListForReport(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"select 
                            dimension_id as 维度编号,
                            dimension_class_nm as 维度类别,
                            dimension_name as 维度名称,
                            dimension_description as [维度定义(维度描述)],
                            (select count(*)  from dbo.tb_Question where question_dimensionid = dimension_id) as 维度题目总数,
                            dimension_time_min as [维度答题时间(秒)],
                            (select count(*)  from dbo.tb_Question where question_dimensionid = dimension_id and question_status=1) as 启用题目数量,
                            (select count(*)  from dbo.tb_Question where question_dimensionid = dimension_id and question_status=0) as 禁用题目数量,
                            dimension_2 as P2,
                            dimension_8 as P8,
                            dimension_17 as P17,
                            dimension_sinterval as Q1,
                            dimension_32 as P32,
                            dimension_50 as P50,
                            dimension_68 as P68,
                            dimension_linterval as Q3,
                            dimension_83 as P83,
                            dimension_92 as P92,
                            dimension_98 as P98                     
                           ");
            if (strWhere.Trim() == "")
            {
                strSql.Append(@"FROM dbo.tb_Dimension,dbo.tb_Dimension_Class 
                                where dbo.tb_Dimension.dimension_class_id = dbo.tb_Dimension_Class.dimension_class_id");
            }
            else
            {
                strSql.Append(@" FROM dbo.tb_Dimension,dbo.tb_Dimension_Class  where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());

        }
        public static DataSet GetDefaultListByPosID(string posID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"select 
                           dimension_id,
                           dimension_name,
                           dimension_description,
                            dimension_time_min/60 as dimension_time_min
                           ");
            strSql.Append(" FROM dbo.tb_Dimension ");
            strSql.Append(@"where dimension_id in(select dimension_id 
						    from dbo.tb_Position_Default_Dimension 
						    where position_id = ");
            strSql.Append(posID);
            strSql.Append(")");

            return DbHelperSQL.Query(strSql.ToString());
        }

        public static DataSet GetDimensionSetByActID(string actID)
        {
              StringBuilder strSql = new StringBuilder();
            strSql.Append(@"select 
                           activity_id,
                           tb_Activity_Dimension.dimension_id,
                           dimension_weight,
                           dimension_name,
                           dimension_description, 
                            dimension_time_min/60 as dimension_time_min
                           ");
            strSql.Append(" FROM dbo.tb_Activity_Dimension,dbo.tb_Dimension ");
            strSql.Append(@"where tb_Activity_Dimension.dimension_id= dbo.tb_Dimension.dimension_id
						    and activity_id = ");
            strSql.Append(actID);

            return DbHelperSQL.Query(strSql.ToString());
        }

        public static int SetPositionDimension(int positionID,int dimID,float weight)
        {
            string posID=positionID.ToString();
            string dim=dimID.ToString();

            StringBuilder strSql = new StringBuilder();
            strSql.Append("if exists(SELECT * FROM dbo.tb_Position_Default_Dimension WHERE position_id="+posID+" and dimension_id="+dim+")");
            strSql.Append("delete from dbo.tb_Position_Default_Dimension WHERE position_id=" + posID);
            
            DbHelperSQL.ExecuteSql(strSql.ToString());
            strSql.Clear();

            strSql.Append("insert into dbo.tb_Position_Default_Dimension(");
            strSql.Append(@"position_id,
                            dimension_id,
                            dimension_weight)");
            strSql.Append(" values (");
            strSql.Append(posID).Append(",");
            strSql.Append(dim).Append(",");
            strSql.Append(weight.ToString()).Append(")");
            
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            return rows;
        }

        public static int SetActivityDimension(int activityID, int dimID, float weight)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("if exists(SELECT * FROM dbo.tb_Activity_Dimension WHERE activity_id=" + activityID + " and dimension_id=" + dimID + ")");
            strSql.Append(" delete from dbo.tb_Activity_Dimension WHERE activity_id=" + activityID);
            DbHelperSQL.ExecuteSql(strSql.ToString());
            strSql.Clear();

            strSql.Append("insert into dbo.tb_Activity_Dimension(");
            strSql.Append(@"activity_id,
                            dimension_id,
                            dimension_weight)");
            strSql.Append(" values (");
            strSql.Append(activityID.ToString()).Append(",");
            strSql.Append(dimID.ToString()).Append(",");
            strSql.Append(weight.ToString()).Append(")");

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            return rows;
        }

        public static int GetDimensionTime(int dimID)
        {
            int ret =0;

            StringBuilder strSql = new StringBuilder();

            strSql.Append(@"select dimension_time_min from dbo.tb_Dimension where dimension_id = ");
            strSql.Append("'");
            strSql.Append(dimID );
            strSql.Append("'");
            try
            {
                DataSet Dimends = DbHelperSQL.Query(strSql.ToString());
                if (Dimends != null && Dimends.Tables[0].Rows.Count == 1)
                {
                    ret = Int32.Parse(Dimends.Tables[0].Rows[0][0].ToString());
                   
                }
                return ret;
            }
            catch
            {
                ret = -1;
                return ret;
            }
        }

        public static int GetDimensionQuestionNm(int dimID)
        {

            int ret = 0;

            StringBuilder strSql = new StringBuilder();

            strSql.Append(@"select count(*) from dbo.tb_Question where question_dimensionid = ");
            strSql.Append("'");
            strSql.Append(dimID);
            strSql.Append("'");
            try
            {
                DataSet Dimends = DbHelperSQL.Query(strSql.ToString());
                if (Dimends != null && Dimends.Tables[0].Rows.Count == 1)
                {
                    ret = Int32.Parse(Dimends.Tables[0].Rows[0][0].ToString());

                }
                return ret;
            }
            catch
            {
                ret = -1;
                return ret;
            }
        }
        /// <summary>
        /// 删除一条数据
        /// </summary>
        /// <param name="activity_id"></param>
        public static void Delete(int dimension_id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete dbo.tb_Dimension ");
            strSql.Append(" where dimension_id=@IdDimension");
            SqlParameter[] parameters = {
                                            new SqlParameter("@IdDimension",SqlDbType.Int,4)
                                        };
            parameters[0].Value = dimension_id;
            DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public static int Add(Dimension model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into dbo.tb_Dimension(");
            strSql.Append(@"dimension_name,
                            dimension_description,
                            dimension_class_id,
                            dimension_time_min,
                            dimension_sinterval,
                            dimension_minterval,
                            dimension_linterval,
                            dimension_98,
                            dimension_92,
                            dimension_83,
                            dimension_68,
                            dimension_50,
                            dimension_32,
                            dimension_17,
                            dimension_8,
                            dimension_2,dimension_time_xiaxian,dimension_time_shangxian)");

            strSql.Append(" values (");

            strSql.Append(@"@dimension_name,
                            @dimension_description,
                            @dimension_class_id,
                            @dimension_time_min,
                            @dimension_sinterval,
                            @dimension_minterval,
                            @dimension_linterval,
                            @dimension_98,
                            @dimension_92,
                            @dimension_83,
                            @dimension_68,
                            @dimension_50,
                            @dimension_32,
                            @dimension_17,
                            @dimension_8,
                            @dimension_2,@dimension_time_xiaxian,@dimension_time_shangxian)");

            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@dimension_name", SqlDbType.NVarChar,255),
					new SqlParameter("@dimension_description", SqlDbType.NVarChar,255),
					new SqlParameter("@dimension_class_id", SqlDbType.Int,4),
					new SqlParameter("@dimension_time_min", SqlDbType.Int,4),
                    new SqlParameter("@dimension_sinterval",SqlDbType.Float),
					new SqlParameter("@dimension_minterval", SqlDbType.Float),
					new SqlParameter("@dimension_linterval", SqlDbType.Float),
                    new SqlParameter("@dimension_98", SqlDbType.Float),
                    new SqlParameter("@dimension_92", SqlDbType.Float),
                    new SqlParameter("@dimension_83", SqlDbType.Float ),
                    new SqlParameter("@dimension_68", SqlDbType.Float),
                    new SqlParameter("@dimension_50", SqlDbType.Float),
                    new SqlParameter("@dimension_32", SqlDbType.Float),
                    new SqlParameter("@dimension_17", SqlDbType.Float),
                    new SqlParameter("@dimension_8", SqlDbType.Float),
                    new SqlParameter("@dimension_2", SqlDbType.Float),

                    new SqlParameter("@dimension_time_xiaxian", SqlDbType.Int,4),
                    new SqlParameter("@dimension_time_shangxian", SqlDbType.Int,4)
                                        };

            parameters[0].Value = model.Dimnm;
            parameters[1].Value = model.Dimension_descrip;
            parameters[2].Value = model.Dimension_classid;
            parameters[3].Value = model.Time_min;
            parameters[4].Value = model.Sinterval;
            parameters[5].Value = model.Minterval;
            parameters[6].Value = model.Linterval;
            parameters[7].Value = model.Dimension_98;
            parameters[8].Value = model.Dimension_92;
            parameters[9].Value = model.Dimension_83;
            parameters[10].Value = model.Dimension_68;
            parameters[11].Value = model.Dimension_50;
            parameters[12].Value = model.Dimension_32;
            parameters[13].Value = model.Dimension_17;
            parameters[14].Value = model.Dimension_8;
            parameters[15].Value = model.Dimension_2;

            parameters[16].Value = model.Time_xiaxian;
            parameters[17].Value = model.Time_shangxian;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return -1;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public static Dimension GetModel(int dimension_id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 * from dbo.tb_Dimension");
            strSql.Append(" where dimension_id=@ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)};
            parameters[0].Value = dimension_id;

            Dimension model = new Dimension();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["dimension_id"].ToString() != "")
                {
                    model.Dimension_id = int.Parse(ds.Tables[0].Rows[0]["dimension_id"].ToString());
                }

                model.Dimnm = ds.Tables[0].Rows[0]["dimension_name"].ToString();
                model.Dimension_descrip = ds.Tables[0].Rows[0]["dimension_description"].ToString();
                model.Dimension_classid = int.Parse(ds.Tables[0].Rows[0]["dimension_class_id"].ToString());
                model.Time_min = (int)ds.Tables[0].Rows[0]["dimension_time_min"];
                model.Sinterval = float.Parse(ds.Tables[0].Rows[0]["dimension_sinterval"].ToString());
                model.Minterval = float.Parse(ds.Tables[0].Rows[0]["dimension_minterval"].ToString());
                model.Linterval = float.Parse(ds.Tables[0].Rows[0]["dimension_linterval"].ToString());
                model.Dimension_classid = (int)ds.Tables[0].Rows[0]["dimension_class_id"];
                model.Time_min = (int)ds.Tables[0].Rows[0]["dimension_time_min"];
                model.Dimension_98 = float.Parse(ds.Tables[0].Rows[0]["dimension_98"].ToString());
                model.Dimension_92 = float.Parse(ds.Tables[0].Rows[0]["dimension_92"].ToString());
                model.Dimension_83 = float.Parse(ds.Tables[0].Rows[0]["dimension_83"].ToString());
                model.Dimension_68 = float.Parse(ds.Tables[0].Rows[0]["dimension_68"].ToString());
                model.Dimension_50 = float.Parse(ds.Tables[0].Rows[0]["dimension_50"].ToString());
                model.Dimension_32 = float.Parse(ds.Tables[0].Rows[0]["dimension_32"].ToString());
                model.Dimension_17 = float.Parse(ds.Tables[0].Rows[0]["dimension_17"].ToString());
                model.Dimension_8 = float.Parse(ds.Tables[0].Rows[0]["dimension_8"].ToString());
                model.Dimension_2 = float.Parse(ds.Tables[0].Rows[0]["dimension_2"].ToString());

                if (false == ds.Tables[0].Rows[0]["dimension_time_xiaxian"].Equals(DBNull.Value))
                {
                    model.Time_xiaxian = Int32.Parse(ds.Tables[0].Rows[0]["dimension_time_xiaxian"].ToString());
                }
                if (false == ds.Tables[0].Rows[0]["dimension_time_shangxian"].Equals(DBNull.Value))
                {
                    model.Time_shangxian = Int32.Parse(ds.Tables[0].Rows[0]["dimension_time_shangxian"].ToString());
                }
                return model;
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public static bool Exists(int useId)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select count(1) from dbo.tb_Dimension");

            strSql.Append(" where dimension_id=@dimension_id ");

            SqlParameter[] parameters = { new SqlParameter("@dimension_id", SqlDbType.Int, 4) };

            parameters[0].Value = useId;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public static int Update(Dimension model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update dbo.tb_Dimension set ");
            strSql.Append("dimension_name=@dimension_name,");
            strSql.Append("dimension_description=@dimension_description,");
            strSql.Append("dimension_class_id=@dimension_class_id,");
            strSql.Append("dimension_time_min=@dimension_time_min,");
            strSql.Append("dimension_sinterval=@dimension_sinterval,");
            strSql.Append("dimension_minterval=@dimension_minterval,");
            strSql.Append("dimension_linterval=@dimension_linterval,");
            strSql.Append("dimension_98=@dimension_98,");
            strSql.Append("dimension_92=@dimension_92,");
            strSql.Append("dimension_83=@dimension_83,");
            strSql.Append("dimension_68=@dimension_68,");
            strSql.Append("dimension_50=@dimension_50,");
            strSql.Append("dimension_32=@dimension_32,");
            strSql.Append("dimension_17=@dimension_17,");
            strSql.Append("dimension_8=@dimension_8,");
            strSql.Append("dimension_2=@dimension_2,");

            strSql.Append("dimension_time_xiaxian=@dimension_time_xiaxian,");
            strSql.Append("dimension_time_shangxian=@dimension_time_shangxian ");

            strSql.Append(" where dimension_id=@dimension_id ");
            SqlParameter[] parameters = {
                    new SqlParameter("@dimension_id", SqlDbType.Int),                                
				    new SqlParameter("@dimension_name", SqlDbType.NVarChar,255),
					new SqlParameter("@dimension_description", SqlDbType.NVarChar,255),
					new SqlParameter("@dimension_class_id", SqlDbType.Int,4),
					new SqlParameter("@dimension_time_min", SqlDbType.Int,4),

                    new SqlParameter("@dimension_sinterval",SqlDbType.Float),
					new SqlParameter("@dimension_minterval", SqlDbType.Float),
					new SqlParameter("@dimension_linterval", SqlDbType.Float),

                    new SqlParameter("@dimension_98", SqlDbType.Float),
                    new SqlParameter("@dimension_92", SqlDbType.Float),
                    new SqlParameter("@dimension_83", SqlDbType.Float),
                    new SqlParameter("@dimension_68", SqlDbType.Float),
                    new SqlParameter("@dimension_50", SqlDbType.Float),
                    new SqlParameter("@dimension_32", SqlDbType.Float),
                    new SqlParameter("@dimension_17", SqlDbType.Float),
                    new SqlParameter("@dimension_8", SqlDbType.Float),
                    new SqlParameter("@dimension_2", SqlDbType.Float),
                                        
                    new SqlParameter("@dimension_time_xiaxian", SqlDbType.Int,4),
                    new SqlParameter("@dimension_time_shangxian", SqlDbType.Int,4) };

            parameters[0].Value = model.Dimension_id;
            parameters[1].Value = model.Dimnm;
            parameters[2].Value = model.Dimension_descrip;
            parameters[3].Value = model.Dimension_classid;
            parameters[4].Value = model.Time_min;
            parameters[5].Value = model.Sinterval;
            parameters[6].Value = model.Minterval;
            parameters[7].Value = model.Linterval;
            parameters[8].Value = model.Dimension_98;
            parameters[9].Value = model.Dimension_92;
            parameters[10].Value = model.Dimension_83;
            parameters[11].Value = model.Dimension_68;
            parameters[12].Value = model.Dimension_50;
            parameters[13].Value = model.Dimension_32;
            parameters[14].Value = model.Dimension_17;
            parameters[15].Value = model.Dimension_8;
            parameters[16].Value = model.Dimension_2;

            parameters[17].Value = model.Time_xiaxian;
            parameters[18].Value = model.Time_shangxian;

            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);

        }
    }
}
